import datetime
import re
import pymysql
from dateutil.relativedelta import relativedelta
from flask import current_app, request
from utilsapp import utils
import traceback


def get_8(arr):
    ll = len(arr)
    for i in range(ll):
        if type(arr[i]) == datetime.datetime:
            arr[i] = arr[i] + datetime.timedelta(hours=-8)


def get_conn(db_name=None, dbconfig=None):
    d = current_app.config['settings']['mysql']
    db_name = d['dbname'] if db_name is None else db_name
    dbconfig = d if dbconfig is None else dbconfig

    return pymysql.connect(host=dbconfig['host'], user=dbconfig['user'], port=dbconfig['port'],
                           password=dbconfig['password'], db=db_name, charset='utf8', connect_timeout=10,
                           read_timeout=10, write_timeout=10)


# 查询返回数据，数组类型
def db_connect(sql, _conn=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor()
    try:
        for i in range(2):
            try:
                cur.execute(sql)
                datas = cur.fetchall()
                fields = [field[0] for field in cur.description]
                res = [dict(zip(fields, result)) for result in datas]
                return {'msg': '查询成功', 'code': 0, 'data': res, 'success': True}
            except Exception as e:
                error_message = traceback.format_exc()
                current_app.add_log('db_connect', error_message)
                match = re.search(r"\\\\ieplat\\\\([^']+)'", error_message)
                if match:
                    result = match.group(1)
                    cur.execute(f'REPAIR  TABLE {result};')
                else:
                    return {'msg': '查询失败', 'code': 0, 'data': [], 'error': str(e), 'success': False}

    finally:
        cur.close()
        conn.close() if _conn is None else ''


# 查询影响行数
def db_operation(sql, _conn=None, _cur=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor() if _cur is None else _cur
    try:
        cur.execute(sql)
        return {"Affected_rows": cur.rowcount, 'success': True}
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_operation', error_message)
        return {"Affected_rows": 0, 'msg': '网络异常,请稍候重试', 'error': str(e), 'success': False}
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''


# 插入，可批量
def db_insert(tableName, adddataList, not_db_keys: list = [], _conn=None, _cur=None):
    if adddataList is None or len(adddataList) <= 0:
        return utils.fail('数据为空')
    conn = current_app.db_pool.connection() if _conn is None else _conn
    cur = conn.cursor() if _cur is None else _cur
    try:
        # 排除不需要添加的字段key
        # 此处可以在前端Model中添加['not_db_keys']=[] 来排除需要添加的字段，前端为主
        if type(adddataList) == dict and 'not_db_keys' in adddataList:
            not_db_keys = adddataList['not_db_keys']

        dataList = [adddataList] if type(adddataList) == dict else adddataList
        not_db_keys = ['id', 'not_db_keys'] + not_db_keys
        add_models = []
        for item in dataList:
            model = {key: item[key] for key in item if key not in not_db_keys}
            add_models.append({k: model[k] for k in sorted(model)})

        columns = '`, `'.join(add_models[0].keys())
        values = ', '.join(['%s'] * len(add_models[0]))
        sql = f"INSERT INTO `{tableName}` (`{columns}`) VALUES ({values})"
        params = [tuple(row.values()) for row in add_models]
        cur.executemany(sql, params)
        lastrowid = cur.lastrowid

        return {"Affected_rows": cur.rowcount, "lastrowid": lastrowid, 'msg': '新增成功', 'code': 0, 'success': True, }
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_insert', error_message)
        return {"Affected_rows": 0, 'error': str(e), 'msg': '新增失败', 'code': -1, 'success': False, }
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''

def db_insert_id(tableName, adddataList, not_db_keys: list = [], _conn=None, _cur=None):
    if adddataList is None or len(adddataList) <= 0:
        return utils.fail('数据为空')
    conn = current_app.db_pool.connection() if _conn is None else _conn
    cur = conn.cursor() if _cur is None else _cur
    try:
        # 排除不需要添加的字段key
        # 此处可以在前端Model中添加['not_db_keys']=[] 来排除需要添加的字段，前端为主
        if type(adddataList) == dict and 'not_db_keys' in adddataList:
            not_db_keys = adddataList['not_db_keys']

        dataList = [adddataList] if type(adddataList) == dict else adddataList
        add_models = []
        for item in dataList:
            model = {key: item[key] for key in item if key not in not_db_keys}
            add_models.append({k: model[k] for k in sorted(model)})

        columns = '`, `'.join(add_models[0].keys())
        values = ', '.join(['%s'] * len(add_models[0]))
        sql = f"INSERT INTO `{tableName}` (`{columns}`) VALUES ({values})"
        params = [tuple(row.values()) for row in add_models]
        cur.executemany(sql, params)
        lastrowid = cur.lastrowid

        return {"Affected_rows": cur.rowcount, "lastrowid": lastrowid, 'msg': '新增成功', 'code': 0, 'success': True, }
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_insert', error_message)
        return {"Affected_rows": 0, 'error': str(e), 'msg': '新增失败', 'code': -1, 'success': False, }
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''


# 更新
def db_update(tableName, model, where_clause, where_model, _conn=None, _cur=None):
    conn = current_app.db_pool.connection() if _conn is None else _conn
    cur = conn.cursor() if _cur is None else _cur
    try:
        # 排除需要修改的字段,默认添加id字段,前端可以在model传入not_db_keys
        not_db_keys = model['not_db_keys'] + ['id', 'not_db_keys'] if type(
            model) == dict and 'not_db_keys' in model else ['id']
        db_model = {key: model[key] for key in model if key not in not_db_keys}

        # 列的字段
        set_clause = ', '.join(
            [f"`{column}` = %s" for column in db_model.keys()])

        sql = f"UPDATE {tableName} SET {set_clause} {where_clause}"
        cur.execute(sql, tuple(list(db_model.values()) +
                               list(where_model.values())))

        return {"Affected_rows": cur.rowcount, 'msg': '修改成功', 'code': 0, 'success': True, }
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_update', error_message)
        return {"Affected_rows": 0, 'error': str(e), 'msg': '修改失败', 'code': -1, 'success': False, }
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''


# 删除
def db_delete(tableName, where_clause, whereObj, _conn=None, _cur=None):
    conn = current_app.db_pool.connection() if _conn is None else _conn
    cur = conn.cursor() if _cur is None else _cur
    try:
        # 列的字段
        # Build the SQL UPDATE statement
        sql = f"DELETE FROM {tableName} {where_clause}"
        cur.execute(sql, tuple(whereObj.values()))
        return {"Affected_rows": cur.rowcount, 'msg': '删除成功', 'code': 0, 'success': True, }
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_delete', error_message)
        return {"Affected_rows": 0, 'error': str(e), 'msg': '删除失败', 'code': -1, 'success': False, }
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''

def db_deletes(sql, _conn=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor()
    try:
        cur.execute(sql)
        return {"Affected_rows": cur.rowcount, 'msg': '删除成功', 'code': 0, 'success': True, }
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_delete', error_message)
        return {"Affected_rows": 0, 'error': str(e), 'msg': '删除失败', 'code': -1, 'success': False, }
    finally:
        conn.close() if _conn is None else ''


# 单表分页列表查询
def db_table_pagelist(table, page, where='', size=20):
    sql = f"SELECT * FROM {table} {where}"
    return db_pagelist(sql, page, size)


# SQL分页列表查询
def db_pagelist(sql, page, size=20, _conn=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor()
    try:
        sql_count = re.sub(r'SELECT(.|\s|\S)*?FROM',
                           'SELECT COUNT(1) FROM', sql, count=1, flags=re.IGNORECASE)
        cur.execute(sql_count)
        query_count = cur.fetchone()
        first_value = query_count[0] if query_count else 0
        if first_value <= 0:
            return {'msg': '数据库查询成功', 'code': 0, 'data': [],
                    'pagination': {"page": page, "pageSize": size, "total": first_value}}
        cur.execute(sql + f' LIMIT {max(0, (page - 1) * size)},{size}')
        datas = cur.fetchall()
        fields = [field[0] for field in cur.description]
        res = [dict(zip(fields, result)) for result in datas]
        return {'msg': '数据库查询成功', 'code': 0, 'success': True, 'data': res,
                'pagination': {"page": page, "pageSize": size, "total": first_value}}
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_pagelist', error_message)
        return {'msg': '查询失败', 'code': -1, 'data': [], 'error': str(e), 'success': False}
    finally:
        cur.close()
        conn.close()


# 单表列表条件查询
def db_table_list(table, where='', where_param=None):
    sql = f"SELECT * FROM {table} {where}"
    return db_list(sql, where_param)


# SQL列表条件查询
def db_list(sql, where_param: dict = None, _conn=None, _cur=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor() if _cur is None else _cur
    try:
        for i in range(2):
            try:
                params = None if where_param is None else tuple(where_param.values())
                cur.execute(sql, params)
                datas = cur.fetchall()
                fields = [field[0] for field in cur.description]
                res = [dict(zip(fields, result)) for result in datas]
                return {'msg': '查询成功', 'code': 0, 'data': res, 'success': True}
            except Exception as e:
                error_message = traceback.format_exc()
                current_app.add_log('db_list', error_message)
                match = re.search(r"\\\\ieplat\\\\([^']+)'", error_message)
                if match:
                    result = match.group(1)
                    cur.execute(f'REPAIR  TABLE {result};')
                else:
                    return {'msg': '查询失败', 'code': -1, 'data': [], 'error': str(e), 'success': False}
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''



# SQL首行首列查询
def db_fetchone(sql, _conn=None, _cur=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor() if _cur is None else _cur
    try:
        cur.execute(sql)
        q = cur.fetchone()
        if q == None:
            return {'msg': '查询不到符合条件的数据', 'success': True, 'code': 0, 'data': '', 'Affected_rows': cur.rowcount}
        first_value = q[0] if q[0] is not None else ''
        return {'msg': '数据库查询成功', 'success': True, 'code': 0, 'data': first_value, 'Affected_rows': cur.rowcount}
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_fetchone', error_message)
        return {'msg': '查询失败', 'code': -1, 'data': '', 'error': str(e), 'Affected_rows': 0, 'success': False}
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''


def db_fetchall(sql, _conn=None, _cur=None):
    conn = current_app.db_pool.connection()
    cur = conn.cursor() if _cur is None else _cur
    try:
        cur.execute(sql)
        q = cur.fetchall()
        first_value = q if q is not None else ''
        return {'msg': '数据库查询成功', 'success': True, 'code': 0, 'data': first_value, 'Affected_rows': cur.rowcount}
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('db_fetchone', error_message)
        return {'msg': '查询失败', 'code': -1, 'data': '', 'error': str(e), 'Affected_rows': 0, 'success': False}
    finally:
        conn.close() if _conn is None else ''
        cur.close() if _cur is None else ''



def get_sensor_new_table(s_id=None, s_address=None, s_port=None):
    try:
        if s_id is not None:
            sql = f'''
            SELECT s.port s_port,s.address s_address FROM sensor s
            WHERE s.id = {s_id}
            '''
            res = db_list(sql)
            if not res['success'] and len(res['data']) <= 0:
                return ''
            s_address = res['data'][0]['s_address']
            s_port = res['data'][0]['s_port']

        tbs = []
        for m in range(3):
            now = datetime.datetime.now() - relativedelta(months=m)
            date = now.strftime('%Y%m')
            t_name = f'd_{s_port}_{s_address}_{date}'
            tbs.append(f"(SELECT COUNT(1) c,'{t_name}' tname FROM `{t_name}`)")
        if len(tbs) <= 0:
            return None
        sql = 'SELECT tb.`tname` FROM (' + ' UNION ALL '.join(
            tbs) + ') tb where tb.c>0  ORDER BY tb.tname desc  LIMIT 0,1'
        res = db_fetchone(sql, _conn='conn')
        fvalue = res['data']
        if fvalue is not   None and len(fvalue) > 0:
            return fvalue[0] if type(fvalue) == list else fvalue
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log('get_sensor_new_table', error_message)
        print('get_sensor_new_table for 异常', str(e))

    return None

def create_table(_conn, port, address, db_name):
    if not has_db_name(db_name, _conn):
        # 创建数据库
        dbres = db_fetchone(f'CREATE DATABASE {db_name}')
        if dbres['code'] != 0:
            return dbres

    _conn.select_db(db_name)
    cur = _conn.cursor()
    try:
        cur.execute('SHOW TABLES;')
        datas = cur.fetchall()
        cur.close()
        tbs = [t[0] for t in datas]

        _conn.begin()
        cur = _conn.cursor()
        for i in range(3):
            now = datetime.datetime.now() + relativedelta(months=i)
            date = now.strftime('%Y%m')
            name = f'_{port}_{address}_{date}'
            if 'd' + name in tbs:
                continue
            sql = f'''
                        CREATE TABLE `d{name}`  (
                          `id` int(11) NOT NULL AUTO_INCREMENT,
                          `address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '传感器地址',
                          `port` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '站点地址',
                          `feature_key` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '特征值key',
                          `value` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '点数据值',
                          `time` datetime NULL DEFAULT NULL COMMENT '时间戳',
                          `state` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '停开机状态，开机1，停机0',
                          PRIMARY KEY (`id`) USING BTREE,
                          INDEX `时间`(`time`) USING BTREE,
                          INDEX `类型`(`feature_key`) USING BTREE
                        ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
                        '''
            cur.execute(sql)
            sql = f'''
                        CREATE TABLE `s{name}`  (
                          `id` int(11) NOT NULL AUTO_INCREMENT,
                          `feature_key` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '特征值key',
                          `value` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '点数据值',
                          `time` datetime NULL DEFAULT NULL COMMENT '时间戳',
                          PRIMARY KEY (`id`) USING BTREE,
                          INDEX `时间`(`time`) USING BTREE,
                          INDEX `类型`(`feature_key`) USING BTREE
                        ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
                        '''
            cur.execute(sql)
        _conn.commit()
        return True
    except Exception as e:
        _conn.rollback()
        print('【异常】create_table', str(e))
        return False
    finally:
        cur.close()
        _conn.close()


# 判断数据库是否存在
def has_db_name(db_name, _conn):
    dbres = db_list('SHOW DATABASES', _conn)
    if dbres['code'] == 0 and len(dbres['data']) > 0:
        return db_name in [t['Database'] for t in dbres['data']]
    return False


# 根据配置文件获取数据表名
def get_table_name(address, port, prefix='s', add_month=0, date=''):
    now = datetime.datetime.now() + relativedelta(months=add_month)
    date = date if len(date) > 0 else now.strftime('%Y%m')
    ns = [prefix, address, port, date]
    return '_'.join(map(str, ns))